/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.unesp.demac.bcc.soo.dao;

import br.unesp.demac.bcc.soo.bean.Aluguel;
import br.unesp.demac.bcc.soo.bean.AluguelRel;
import br.unesp.demac.bcc.soo.bean.Dvd;
import br.unesp.demac.bcc.soo.bean.Pessoa;
import br.unesp.demac.bcc.soo.util.FabricaConexao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author flavioam
 */
public class AluguelDAOImp implements AluguelDAO {

    @Override
    public boolean salvaAluguel(Aluguel al) throws Exception {

        boolean saved = false;
        Connection con = null;

        try {

            con = FabricaConexao.getConexao();
            con.setAutoCommit(false);

            String sqlAluguel = "insert into Alugueis (dataLocacao, valor, Pessoas_idPessoas) values (current_date(),?,?)";

            PreparedStatement pstm = con.prepareStatement(sqlAluguel, PreparedStatement.RETURN_GENERATED_KEYS);

            pstm.setDouble(1, al.getValor());
            pstm.setInt(2, al.getPessoa().getIdPessoa());

            pstm.executeUpdate();

            ResultSet keys = pstm.getGeneratedKeys();
            keys.next();
            al.setIdAluguel(keys.getInt(1));

            for (AluguelRel aRel : al.getAluguelRel()) {

                String sqlRel = "insert into RelAlugueisDvds (Alugueis_idAlugueis, Dvds_idDvds, valor, dataDevolucao) values (?,?,?,?)";

                PreparedStatement pstmRel = con.prepareStatement(sqlRel);
                pstmRel.setInt(1, al.getIdAluguel());
                pstmRel.setInt(2, aRel.getDvd().getIdDvd());
                pstmRel.setDouble(3, aRel.getDvd().getModalidade().getValorAluguel());
                pstmRel.setDate(4, new java.sql.Date(aRel.getDataDevolucao().getTime()));
                pstmRel.executeUpdate();
            }

            con.commit();
            saved = true;

        } catch (SQLException e) {
            throw new Exception("Erro ao salvar aluguel: " + e.getMessage());
        } finally {

            FabricaConexao.fechar(con);
        }
        return saved;
    }

    @Override
    public ArrayList<Aluguel> selecionaAlugueisAtivosByDvd(Dvd d) throws Exception {

        ArrayList<Aluguel> l = new ArrayList<Aluguel>();
        Connection con = null;

        try {

            con = FabricaConexao.getConexao();

            String sqlAlugueis = "select idAlugueis, DATE(dataLocacao) as dataLocacao, valor, Pessoas_idPessoas from Alugueis where Pessoas_idPessoas in(select Pessoas_idPessoas from Alugueis where idAlugueis in(select Alugueis_idAlugueis from RelAlugueisDvds where dataDevolvido is null and Dvds_idDvds = ?))";

            PreparedStatement pstmAlugueis = con.prepareStatement(sqlAlugueis);

            pstmAlugueis.setInt(1, d.getIdDvd());

            ResultSet rsAlugueis = pstmAlugueis.executeQuery();

            while (rsAlugueis.next()) {

                Aluguel a = new Aluguel();
                a.setDataLocacao(rsAlugueis.getDate("dataLocacao"));
                a.setIdAluguel(rsAlugueis.getInt("idAlugueis"));
                a.setValor(rsAlugueis.getDouble("valor"));

                PessoaDAO pDao = new PessoaDAOImp();
                a.setPessoa(pDao.selecionaClienteById(rsAlugueis.getInt("Pessoas_idPessoas")));

                String sqlRel = "select * from RelAlugueisDvds where Alugueis_idAlugueis = ? and dataDevolvido is null";

                PreparedStatement pstmRel = con.prepareStatement(sqlRel);
                pstmRel.setInt(1, a.getIdAluguel());

                ResultSet rsRel = pstmRel.executeQuery();

                ArrayList<AluguelRel> relList = new ArrayList<AluguelRel>();

                while (rsRel.next()) {

                    AluguelRel aRel = new AluguelRel();
                    aRel.setDataDevolucao(rsRel.getDate("dataDevolucao"));
                    aRel.setDataDevolvido(rsRel.getDate("dataDevolvido"));
                    aRel.setValor(rsRel.getDouble("valor"));

                    DvdDAO dDao = new DvdDAOImp();

                    aRel.setDvd(dDao.selecionaDvdById(rsRel.getInt("Dvds_idDvds")));

                    relList.add(aRel);
                }

                a.setAlguelRel(relList);

                l.add(a);
            }

        } catch (SQLException e) {

            throw new Exception("Erro ao selecionar alugueis ativos: " + e.getMessage());
        } finally {
            FabricaConexao.fechar(con);
        }
        return l;
    }

    @Override
    public boolean efetuaDevolucao(Dvd d) throws Exception {

        boolean saved = false;
        Connection con = null;

        try {

            con = FabricaConexao.getConexao();

            String sql = "update RelAlugueisDvds set dataDevolvido = current_date() where Dvds_idDvds = ? and dataDevolvido is null";

            PreparedStatement pstm = con.prepareStatement(sql);

            pstm.setInt(1, d.getIdDvd());

            pstm.executeUpdate();

            saved = true;

        } catch (SQLException e) {
            throw new Exception("Erro ao efetuar devolução: " + e.getMessage());
        } finally {

            FabricaConexao.fechar(con);
        }
        return saved;
    }
}
